﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using ChinaDynastyTours.Models;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data;
using System.Data.Common;

namespace ChinaDynastyTours.Controls
{
    class UserInfoCtrl
    {
        #region 获取系统用户及相关信息
        
        internal static string[] GetAllUserName()
        {
            string[] userNames;
            Database db = DatabaseFactory.CreateDatabase();
            string sql = string.Format(@"SELECT [Name] FROM aspnet_UserInfo");
            DbCommand dbCommand = db.GetSqlStringCommand(sql);

            DataSet ds = db.ExecuteDataSet(dbCommand);
            if (ds.Tables.Count > 0)
            {
                userNames = new string[ds.Tables[0].Rows.Count];
                for(int i=0;i<ds.Tables[0].Rows.Count;i++)
                {
                    userNames[i] = ds.Tables[0].Rows[i]["Name"].ToString();
                }
                return userNames;
            }
            else
                return null;
        }
        internal static string[] GetAllUserNameInRole(string roleName )
        {
            string[] users = System.Web.Security.Roles.GetUsersInRole(roleName);

            string[] userNames;
            Database db = DatabaseFactory.CreateDatabase();
            StringBuilder formatSql = new StringBuilder();
            formatSql.AppendFormat(@"SELECT [Name] FROM aspnet_UserInfo");

            for (int i = 0; i < users.Length; i++)
            {
                if (i == 0)
                    formatSql.AppendFormat(" WHERE username='{0}'",users[i]);

                formatSql.AppendFormat(" OR username='{0}' ", users[i]);

                //if (i != users.Length - 1)
                //    formatSql.Append(" OR ");
            }
            //formatSql.Append(")");

            DbCommand dbCommand = db.GetSqlStringCommand(formatSql.ToString());

            DataSet ds = db.ExecuteDataSet(dbCommand);
            if (ds.Tables.Count > 0)
            {
                userNames = new string[ds.Tables[0].Rows.Count];
                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    userNames[i] = ds.Tables[0].Rows[i]["Name"].ToString();
                }
                return userNames;
            }
            else
                return null;
        }
        /// <summary>
        /// 根据UserName获取真实姓名
        /// </summary>
        /// <param name="username"></param>
        /// <returns></returns>
        internal static string GetNameByUserName(string username)
        {
            Database db = DatabaseFactory.CreateDatabase();
            string sql = string.Format(@"SELECT [Name] FROM aspnet_UserInfo WHERE UserName='{0}'", username);
            DbCommand dbCommand = db.GetSqlStringCommand(sql);

            return db.ExecuteScalar(dbCommand).ToString();
        }
        /// <summary>
        /// 根据真实姓名获取电话、传真
        /// </summary>
        /// <param name="realname"></param>
        /// <returns></returns>
        internal static DataTable GetTelephoneFaxByUserRealName(string realname)
        {
            Database db = DatabaseFactory.CreateDatabase();
            string sql = string.Format(@"SELECT [Telephone],[Fax] FROM aspnet_UserInfo WHERE Name='{0}'", realname);
            DbCommand dbCommand = db.GetSqlStringCommand(sql);

            DataSet ds = db.ExecuteDataSet(dbCommand);
            if (ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }
        /// <summary>
        /// 通过用户真实姓名，获取EMail地址，由于前端的用户名与用户姓名的添加、修改控制了唯一性(真实姓名)，所以可以通过真实姓名获取EMail
        /// </summary>
        /// <param name="username"></param>
        /// <returns></returns>
        internal static string GetEMailAddressByName(string name)
        {
            Database db = DatabaseFactory.CreateDatabase();
            string sql = string.Format(@"SELECT EMail FROM aspnet_UserInfo WHERE Name='{0}'", name);
            DbCommand dbCommand = db.GetSqlStringCommand(sql);

            return db.ExecuteScalar(dbCommand).ToString();
        }
        /// <summary>
        /// 检测是否已经存在同姓名的人
        /// </summary>
        /// <param name="realname">真实姓名</param>
        /// <returns></returns>
        internal static bool ExistRealName(string realname)
        {
            Database db = DatabaseFactory.CreateDatabase();
            string sql = string.Format(@"SELECT [Name] FROM aspnet_UserInfo WHERE [Name]='{0}'", realname);
            DbCommand dbCommand = db.GetSqlStringCommand(sql);

            return (db.ExecuteScalar(dbCommand) != null);
        }

        /// <summary>
        /// 根据UserName获取个人相关信息
        /// </summary>
        /// <param name="username"></param>
        /// <returns></returns>
        internal static DataTable GetUserInfoData(string username)
        {
            Database db = DatabaseFactory.CreateDatabase();
            //为增加用户'通知’信息，注释掉下面一行，获取全部信息
            //string sql = string.Format(@"SELECT [UserInfoID], [UserName], [IdentificationCardNo], [Name], [Telephone], [Mobile], [Fax], [EMail], [Remark],[DepartmentID],[RankID] FROM aspnet_UserInfo WHERE UserName='{0}'", username);
            string sql = string.Format(@"SELECT * FROM aspnet_UserInfo WHERE UserName='{0}'", username);
            DbCommand dbCommand = db.GetSqlStringCommand(sql);

            DataSet ds = db.ExecuteDataSet(dbCommand);
            if (ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }
        /// <summary>
        /// 获取全部系统用户的'登录用户名'及'姓名'信息
        /// </summary>
        /// <returns></returns>
        internal static DataTable GetUserInfoData()
        {
            Database db = DatabaseFactory.CreateDatabase();
            string sql = "SELECT [UserInfoID], [UserName], [Name] FROM aspnet_UserInfo ui,bas_Department dp where ui.DepartmentID = dp.DepartmentID and dp.DepartmentName <> '导游部'";
            DbCommand dbCommand = db.GetSqlStringCommand(sql);

            DataSet ds = db.ExecuteDataSet(dbCommand);
            if (ds.Tables.Count > 0)
            {
                DataRow dr = ds.Tables[0].NewRow();
                dr["Name"] = "";
                ds.Tables[0].Rows.Add(dr);
                return ds.Tables[0];
            }
            else
                return null;
        }

        /// <summary>
        /// 根据用户名获取用户所属级别，总经理,部门领导,普通员工
        /// </summary>
        /// <param name="username"></param>
        /// <returns></returns>
        internal static int GetUserInfoForRankID(string username)
        {
            Database db = DatabaseFactory.CreateDatabase();
            string sql = string.Format(@"SELECT [RankID] FROM aspnet_UserInfo WHERE UserName='{0}'",username);
            DbCommand dbCommand = db.GetSqlStringCommand(sql);

            object obj = db.ExecuteScalar(dbCommand);
            if (obj != null)
                return int.Parse(obj.ToString());
            else
                return 0;//返回最高级别，总经理
        }
        /// <summary>
        /// 根据用户名获取用户所属部门
        /// </summary>
        /// <param name="username"></param>
        /// <returns></returns>
        internal static string GetUserInfoForDepartmentID(string username)
        {
            Database db = DatabaseFactory.CreateDatabase();
            string sql = string.Format(@"SELECT [DepartmentID] FROM aspnet_UserInfo WHERE UserName='{0}'", username);
            DbCommand dbCommand = db.GetSqlStringCommand(sql);

            object obj = db.ExecuteScalar(dbCommand);
            if (obj != null)
                return obj.ToString();
            else
                return "";//返回0所属的部门，不知是谁，请根据数据表核对
        }

        #endregion

        #region 系统登录用户的基本信息增、删、改

        internal static int  CreateUserInfoData(UserInfoEntity entity)
        {
            Database db = DatabaseFactory.CreateDatabase();

            string sql = string.Format(@"INSERT INTO aspnet_UserInfo ([UserName], [IdentificationCardNo], [Name], [Telephone], [Mobile], [Fax], [EMail], [Remark],[DepartmentID],[RankID],[AllowWebAccess])
                                         VALUES('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}',{8},{9},{10})",
                                        entity.UserName,
                                        entity.IdentificationCardNo,
                                        entity.Name,
                                        entity.Telephone,
                                        entity.Mobile,
                                        entity.Fax,
                                        entity.EMail,
                                        entity.Remark,
                                        entity.DepartmentID,
                                        entity.Rank,
                                        entity.AllowWebAccess);
            return db.ExecuteNonQuery(CommandType.Text, sql);

        }
        internal static int UpdateUserInfo(UserInfoEntity entity)
        {
            Database db = DatabaseFactory.CreateDatabase();
            string sql = string.Format(@"UPDATE aspnet_UserInfo SET [IdentificationCardNo]='{0}', [Name]='{1}', [Telephone]='{2}', [Mobile]='{3}', [Fax]='{4}', [EMail]='{5}', [Remark]='{6}',[DepartmentID]={7},[RankID]={8},[AllowWebAccess]={9} 
                                         WHERE [UserName]='{10}'",
                                         entity.IdentificationCardNo,
                                         entity.Name,
                                         entity.Telephone,
                                         entity.Mobile,
                                         entity.Fax,
                                         entity.EMail,
                                         entity.Remark,
                                         entity.DepartmentID,
                                         entity.Rank,
                                         entity.AllowWebAccess,
                                         entity.UserName);

            return db.ExecuteNonQuery(CommandType.Text, sql);
        }
        internal static int UpdateUserNotifyInfo(string username, int creditLimitRemind, int toursArrivedRemind, int lastMoneyArrivedRemind, int moneyArrivedRemind, int toursLeaveRemind, int prepaymentsRemind, int flightArrivedRemind, int entertainmentRemind, int guideRemind, int ticketsRemind)
        {
            Database db = DatabaseFactory.CreateDatabase();
            string sql = string.Format(@"UPDATE aspnet_UserInfo SET [ToursArrivedRemind]={0}, [LastMoneyArrivedRemind]={1},
                                                                    [MoneyArrivedRemind]={2}, [ToursLeaveRemind]={3}, [PrepaymentsRemind]={4}, [FlightArrivedRemind]={5},
                                                                    [EntertainmentRemind]={6}, [GuideRemind]={7}, [TicketsRemind]={8},[CreditLimitRemind]={9} WHERE [UserName]='{10}'", 
                                                                    toursArrivedRemind, lastMoneyArrivedRemind, moneyArrivedRemind, toursLeaveRemind, prepaymentsRemind, flightArrivedRemind, entertainmentRemind, guideRemind, ticketsRemind,creditLimitRemind, username);

            return db.ExecuteNonQuery(CommandType.Text, sql);
        }
        public static int DeleteUserInfo(string username)
        {
            Database db = DatabaseFactory.CreateDatabase();

            string sql = string.Format(@"DELETE FROM aspnet_UserInfo WHERE UserName='{0}'",username);
            return db.ExecuteNonQuery(CommandType.Text, sql);
        }

        #endregion
    }
}
